using System.Linq;
using SqlSugarLearing.Model;
using SqlSugar;
using SqlSugarLearing.M_Dto;
using System.Collections.Generic;
using Newtonsoft.Json;
using UtilTools;

namespace SqlSugarLearing
{
    public class SqlWorkDemo_Company
    {
        public void GetWorkKPIAdvanceTableTest()
        {
            //获取配置的工作推进表父部门节点ID
            string workKPITableKey = "WorkKPIAdvanceForDepartmentID";
            string configValue = DbHelper.Db.Queryable<KeyValueSetting>().First(it => it.Key == workKPITableKey)?.Value;
            var confiObj = JsonConvert.DeserializeObject<WorkKPIAdvanceForDepartment>(configValue);
            //获取父及其所有子部门节点
            // var departIDs = DbHelper.Db.Queryable<YTDepartMent>().ToTree(it => it.Childs, it => it.DPID, confiObj.DepartmentID).Where(it => it.ValidFlag).ToList()
            //             .SelectMany(it => it.GetAllChildIds()).Select(it => it.DID)
            //             .Append(confiObj.DepartmentID.ToString()).ToList();// 加入父节点id
            List<string> departIDs = DbHelper.Db.SqlQueryable<ViewList>($"select DID as Id from [dbo].[GetAllPartId]({confiObj.DepartmentID})").Select(it => it.Id.ToString()).ToList();
            //获取需要的数据
            var list = DbHelper.Db.Queryable<CRMYTStaff, Operator>((s, o) => s.staffID.ToString() == o.OPCompany)
            .Where((s, o) => departIDs.Contains(s.StaffDepartMent)) // 确定用户部门范围
            .Where((s, o) => o.OPState && o.OPType == "B0006")// 确定用户状态
            .Select<ZFGTB_WorkKPI>((s, o) => new ZFGTB_WorkKPI
            {
                Department = SqlFunc.Subqueryable<YTDepartMent>().Where(dp => s.StaffDepartMent == dp.DID).Select(dp => dp.DName),
                Name = o.OPName,
                ZFOrder = SqlFunc.Subqueryable<CRM_ProcurementOrder>().Where(po => po.CreateByID == o.id).Count(),
                VisitST = SqlFunc.Subqueryable<CRM_VisitCustomer>().Where(v => v.VisitType == "省厅" && v.VisitPerson == o.id).Count(),
                VisitJK = SqlFunc.Subqueryable<CRM_VisitCustomer>().Where(v => v.VisitType == "局科" && v.VisitPerson == o.id).Count(),
                VisitZJ = SqlFunc.Subqueryable<CRM_VisitCustomer>().Where(v => v.VisitType == "专家" && v.VisitPerson == o.id).Count(),
                VisitMYZ = SqlFunc.Subqueryable<CRM_VisitCustomer>().Where(v => v.VisitType == "名园长" && v.VisitPerson == o.id).Count(),
                STMeeting = SqlFunc.Subqueryable<CRM_VisitCustomer>().Where(v => string.IsNullOrEmpty(v.ConveneMeeting) && v.VisitPerson == o.id).Count(),
                STDeclare = SqlFunc.Subqueryable<CRM_VisitCustomer>().Where(v => string.IsNullOrEmpty(v.EnterDirectory) && v.VisitPerson == o.id).Count(),
                NewST = SqlFunc.Subqueryable<CRM_VisitCustomer>().Where(v => v.VisitType == "省厅" && v.IsImportantCustomer == "是" && v.VisitPerson == o.id).Count(),
                NewJK = SqlFunc.Subqueryable<CRM_VisitCustomer>().Where(v => v.VisitType == "局科" && v.IsImportantCustomer == "是" && v.VisitPerson == o.id).Count(),
                NewZJ = SqlFunc.Subqueryable<CRM_VisitCustomer>().Where(v => v.VisitType == "专家" && v.IsImportantCustomer == "是" && v.VisitPerson == o.id).Count(),
                NewMYZ = SqlFunc.Subqueryable<CRM_VisitCustomer>().Where(v => v.VisitType == "名园长" && v.IsImportantCustomer == "是" && v.VisitPerson == o.id).Count(),
                HoldForums = SqlFunc.Subqueryable<CRM_PromotionMeeting>().Where(pm => confiObj.ForumsType.Contains(pm.MeetingType) && pm.CreateByID == o.id).Count()
            })
            .ToList();

            PrintHelper.PrintList(list);
        }
    }

    public class WorkKPIAdvanceForDepartment
    {
        public int DepartmentID { get; set; }

        public List<string> ForumsType { get; set; }
    }

    public class ViewList
    {
        public int Id { get; set; }
    }
}